package com.ruoyi.generateTest.utils;
import com.ruoyi.common.utils.file.FileUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;

import java.io.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
 * @author lmy
 * @date 2024/7/11 15:31
 */
/**
 * word转excel工具类
 *
 * @author top
 * @date 2024/7/10 18:35
 */
@Slf4j
public class WordToExcelUtil {

    //excel写入的路径
    private final static String excelFilePath ="G:\\考试系统项目\\document1\\document_"+System.currentTimeMillis()+".xlsx";
    //模板文件所在的文件路径
    private final static String templateFilePath = "G:\\考试系统项目\\模板.xlsx";


    /**
     *
     * @param file 看是File还是传路径
     * @param whetherParse 0不要解析，1要解析
     * @throws IOException
     */
    public static byte[] wordToExcelGeneral(File file, int whetherParse) throws IOException {

//        File file = new File("F:\\study\\项目\\考试系统\\注安师题库及历年真题(1)\\法律法规题库及历年真题\\2021年安全生产法律法规真题.docx");

        //1.加载文件
        FileInputStream fis = new FileInputStream(file);
        XWPFDocument document = new XWPFDocument(fis);

        StringBuilder text = new StringBuilder();
        for (XWPFParagraph para : document.getParagraphs()) {
            text.append(para.getText()).append("\n");
        }

        String content = text.toString();
        //2.正则
        //格式：1.题目
        String[] questions = content.split("(?=\\b\\d+\\s*\\.)");
        //格式：1、题目
        String[] questionsType2 = content.split("(?=\\b\\d+\\s*、)");
        //格式：第1题【单选题】
        String[] questionsType3 = content.split("(?=第\\d+\\s*题\\s*\\【)");
        //格式：1.【2019年真题】
        String[] questionsType4 = content.split("(?=\\b\\d+\\.\\s*【\\d\\d\\d\\d\\s*年真题】)");
        //特殊格式：案例题
        String[] questions4 = content.split("(?=案\\s*例\\s*\\d+\\s*\\n)");
        //清空不匹配的
        compareAndClearSmallestTwo(questions, questionsType2, questionsType3);

        Pattern pattern = Pattern.compile("^(\\d+)\\s*(.*)(答案：*.*?\\n)(\\【*解析\\】*.*?\\n)", Pattern.DOTALL);
        Pattern pattern1 = Pattern.compile("^(\\d+)\\s*(.*)(答案：*.*?\\n)(\\【*解析\\】*.*?)^\\n", Pattern.DOTALL);
        Pattern patternSpecificType1 = Pattern.compile("^(\\b\\d+\\.)(.*)(\\[答案\\].*?\\n)(\\[解析\\].*?\\n)", Pattern.DOTALL);
        Pattern patternSpecificType2 = Pattern.compile("^(\\d+\\d*)\\s*(、*.*)(\\【参考答案\\】.*?\\n)(\\【老师解析\\】*.*?\\n)", Pattern.DOTALL);
        Pattern patternSpecificType3 = Pattern.compile("^(第\\d+题)(.*)(正确答案.*?\\n)(\\【*答*案*解析\\】*.*?\\n)", Pattern.DOTALL);
        Pattern patternSpecificType4 = Pattern.compile("案\\s*例\\s*\\d\\s*\\n", Pattern.DOTALL);
        Pattern patternSpecificType4ShortAnswer = Pattern.compile("^(\\d+)\\s*(.*)(答：.*?.*)", Pattern.DOTALL);


        // 提取题目 正则
        Pattern compileQuestion = Pattern.compile(".\\s*(.*?)A\\b", Pattern.DOTALL);
        Pattern compileQuestion1 = Pattern.compile(".\\s*(.*?)A\\.", Pattern.DOTALL);
        Pattern compileQuestionType3 = Pattern.compile("题】\\s*(.*?)A\\s*\\.", Pattern.DOTALL);
        Pattern compileQuestionChoicesType4 = Pattern.compile(".\\s*(.*?)A\\s*.", Pattern.DOTALL);
        Pattern compileQuestionShortAnswerType4 = Pattern.compile(".\\s*(.*?)答：\\s*.", Pattern.DOTALL);

        // 匹配多选题 正则
        Pattern compileMultipleChoices = Pattern.compile("多项*选择*题+", Pattern.DOTALL);
        Pattern compileMultipleChoicesType4 = Pattern.compile("[a-fA-F]+,*[a-fA-F]+,*[a-fA-F]*,*[a-fA-F]*,*[a-fA-F]*", Pattern.DOTALL);
        Pattern compileMultipleChoicesType3 = Pattern.compile("[a-fA-F][a-fA-F]+", Pattern.DOTALL);
        Pattern compileMultipleChoicesType5 = Pattern.compile("[a-fA-F],[a-fA-F]+.*", Pattern.DOTALL);
//        Pattern compileMultipleChoicesType1 = Pattern.compile("\\b([a-fA-F](,*[a-fA-F]){1,}|[a-fA-F]{2,})\\b\n", Pattern.DOTALL);
        Pattern compileMultipleChoicesType1 = Pattern.compile("([A-Z]+(?:\\s*,\\s*|\\s+)){1,6}[A-Z]+", Pattern.DOTALL);
        Pattern compileMultipleChoicesType2 = Pattern.compile("答案：\\b([a-fA-F](,[a-fA-F]){1,}|[a-fA-F]{2,})\\b", Pattern.DOTALL);
        // 匹配单选题 正则
        Pattern compileSingleChoices = Pattern.compile("单项*选择*题+", Pattern.DOTALL);
        Pattern compileSingleChoicesType1 = Pattern.compile("\\b[ABCD]\\b(?!,|\\w)\n", Pattern.DOTALL);
        Pattern compileSingleChoicesType2 = Pattern.compile("答案：([ABCD])(?![ABCDEF])(?!,)(?![ABCDEF])\\n", Pattern.DOTALL);
        Pattern compileSingleChoicesType6 = Pattern.compile("答案：([ABCD])(?![ABCDEF])(?!,)(?![ABCDEF])", Pattern.DOTALL);
        Pattern compileSingleChoicesType3 = Pattern.compile("\\b(A|B|C|D)\\b^,", Pattern.DOTALL);
        Pattern compileSingleChoicesType4 = Pattern.compile("\\b(A|B|C|D)\\s*\\nA", Pattern.DOTALL);
        Pattern compileSingleChoicesType5 = Pattern.compile("\\b(A|B|C|D)\\s*\\n", Pattern.DOTALL);
        // 匹配答案的ABCD 正则
        Pattern compileLetter = Pattern.compile("[a-fA-F]+,*\\s*[a-fA-F]*,*\\s*[a-fA-F]*,*\\s*[a-fA-F]*,*\\s*[a-fA-F]*,*\\s*[a-fA-F]*", Pattern.DOTALL);
        // 匹配简答题 正则
        Pattern compileShortAnswerType2 = Pattern.compile("^(第\\d+题【简答题】)(.*)(答案.*?\\n)", Pattern.DOTALL);
        // 匹配判断题 正则
        Pattern compileJudgmentType = Pattern.compile("^(第\\d+题【判断题】)(.*)(答案.*?\\n)(\\[*解析\\]*.*?\\n)", Pattern.DOTALL);

        //3.加载模板文件
        FileInputStream fisTemplate = new FileInputStream(templateFilePath);
        XSSFWorkbook workbook = new XSSFWorkbook(fisTemplate);

        //4.写入文件
        int sheetPage = 0;
        int rowNum = 1;
        int count = 0;
        XSSFSheet sheet = null;
        //类型1
        int countRight = 0;
        for (String question : questions) {
            if (question == null) {
                break;
            }
            Matcher matcher = pattern.matcher(question);
            if (matcher.find()) {
                countRight++;
            }
        }
        int countRight1 = 0;
        for (String s : questionsType4) {
            if (s == null) {
                break;
            }
            Matcher matcher = pattern.matcher(s);
            if (matcher.find()) {
                countRight1++;
            }
        }
        if (countRight1 > countRight) {
            questions[0] = null;
            for (String s : questionsType4) {
                if (s == null) {
                    break;
                }
                Matcher matcher = pattern.matcher(s);
                Matcher matcherSpecificType1 = patternSpecificType1.matcher(s);
                sheet = workbook.getSheetAt(sheetPage);
                Matcher matcherMultipleChoices = compileMultipleChoices.matcher(s);
                Matcher matcherSingleChoices = compileSingleChoices.matcher(s);

                rowNum = findFirstEmptyRow(sheet);

                if (matcher.find()) {
                    XSSFRow row = sheet.createRow(rowNum++);
                    count++;

                    //4.3 提取
                    String serialNumber = matcher.group(1).trim();
                    String fullQuestion = matcher.group(2).trim();
                    String answer = matcher.group(3).trim();
                    String explanation = matcher.group(4).trim();

                    //4.1 如果是多选则换多选页写入
                    Matcher matcherMultipleChoicesType1 = compileMultipleChoicesType2.matcher(answer);
                    Matcher matcher1 = compileMultipleChoicesType2.matcher(answer);
                    if (matcherMultipleChoicesType1.find()) {
                        sheetPage = 1;
                        sheet = workbook.getSheetAt(sheetPage);
                        rowNum = findFirstEmptyRow(sheet);
                        row = sheet.createRow(rowNum++);
                    }
                    Matcher matcherSingleChoicesType1 = compileSingleChoicesType1.matcher(answer);
                    Matcher matcher2 = compileSingleChoicesType2.matcher(answer);
                    if (matcherSingleChoicesType1.find()) {
                        sheetPage = 0;
                        sheet = workbook.getSheetAt(sheetPage);
                        rowNum = findFirstEmptyRow(sheet);
                        row = sheet.createRow(rowNum++);
                    }

                    //公共部分
                    extracted(whetherParse, row, count, compileQuestion1, fullQuestion, compileLetter, answer, explanation);

                    FileOutputStream fos = new FileOutputStream(excelFilePath);
                    workbook.write(fos);
                    fos.close();

                    // Close the doc
                    document.close();
                    fis.close();
                    fisTemplate.close();
                }
            }
        }


        for (String question : questions) {
            if (question == null) {
                break;
            }
            Matcher matcher7 = pattern1.matcher(question);
            Matcher matcher = pattern.matcher(question);
            Matcher matcherSpecificType1 = patternSpecificType1.matcher(question);
            sheet = workbook.getSheetAt(sheetPage);
            Matcher matcherMultipleChoices = compileMultipleChoices.matcher(question);
            Matcher matcherSingleChoices = compileSingleChoices.matcher(question);

            //4.1 单选
            if (matcherSingleChoices.find()) {
                sheetPage = 0;
                sheet = workbook.getSheetAt(sheetPage);
            }
            //4.2 如果是多选则换多选页写入
            if (matcherMultipleChoices.find() && sheetPage == 0) {
                sheet = workbook.getSheetAt(sheetPage++);
            }
            rowNum = findFirstEmptyRow(sheet);

            if (matcher.find()) {
                XSSFRow row = sheet.createRow(rowNum++);
                count++;

                //4.3 提取
                String serialNumber = matcher.group(1).trim();
                String fullQuestion = matcher.group(2).trim();
                String answer = matcher.group(3).trim();
                String explanation = matcher.group(4).trim();

                //4.1 如果是多选则换多选页写入
                Matcher matcherMultipleChoicesType1 = compileMultipleChoicesType1.matcher(answer);
                Matcher matcher4 = compileMultipleChoicesType5.matcher(answer);
                Matcher matcher3 = compileMultipleChoicesType3.matcher(answer);
                if (matcherMultipleChoicesType1.find() || matcher3.find() || matcher4.find()) {
                    sheetPage = 1;
                    sheet = workbook.getSheetAt(sheetPage);
                    rowNum = findFirstEmptyRow(sheet);
                    row = sheet.createRow(rowNum++);
                }
                Matcher matcher8 = compileSingleChoicesType6.matcher(answer);
                Matcher matcherSingleChoicesType1 = compileSingleChoicesType1.matcher(answer);
                Matcher matcher2 = compileSingleChoicesType2.matcher(answer);
                Matcher matcher1 = compileSingleChoicesType3.matcher(answer);
                Matcher matcher5 = compileSingleChoicesType4.matcher(answer);
                Matcher matcher6 = compileSingleChoicesType5.matcher(answer);
                if (matcherSingleChoicesType1.find()
                        || matcher2.find()
                        || matcher1.find()
                        || matcher5.find()
                        || matcher6.find()
                        || matcher8.find()) {
                    sheetPage = 0;
                    sheet = workbook.getSheetAt(sheetPage);
                    rowNum = findFirstEmptyRow(sheet);
                    row = sheet.createRow(rowNum++);
                }

                //公共部分
                extracted(whetherParse, row, count, compileQuestion, fullQuestion, compileLetter, answer, explanation);

                FileOutputStream fos = new FileOutputStream(excelFilePath);
                workbook.write(fos);
                fos.close();

                // Close the doc
                document.close();
                fis.close();
                fisTemplate.close();

            }

            if (matcherSpecificType1.find()) {
                XSSFRow row = sheet.createRow(rowNum++);
                count++;

                //4.3 提取
                String serialNumber = matcherSpecificType1.group(1).trim();
                String fullQuestion = matcherSpecificType1.group(2).trim();
                String answer = matcherSpecificType1.group(3).trim();
                String explanation = matcherSpecificType1.group(4).trim();

                //4.1 如果是多选则换多选页写入
                Matcher matcherMultipleChoicesType1 = compileMultipleChoicesType1.matcher(answer);
                if (matcherMultipleChoicesType1.find() && sheetPage == 0) {
                    sheetPage = 1;
                    sheet = workbook.getSheetAt(sheetPage);
                    rowNum = findFirstEmptyRow(sheet);
                    row = sheet.createRow(rowNum++);
                }
                Matcher matcherSingleChoicesType1 = compileSingleChoicesType1.matcher(answer);
                if (matcherSingleChoicesType1.find() && sheetPage == 1) {
                    sheetPage = 0;
                    sheet = workbook.getSheetAt(sheetPage);
                    rowNum = findFirstEmptyRow(sheet);
                    row = sheet.createRow(rowNum++);
                }

                //公共部分
                extracted(whetherParse, row, count, compileQuestion, fullQuestion, compileLetter, answer, explanation);

                FileOutputStream fos = new FileOutputStream(excelFilePath);
                workbook.write(fos);
                fos.close();

                // Close the doc
                document.close();
                fis.close();
                fisTemplate.close();

            }
            if (matcher7.find()) {
                XSSFRow row = sheet.createRow(rowNum++);
                count++;

                //4.3 提取
                String serialNumber = matcher7.group(1).trim();
                String fullQuestion = matcher7.group(2).trim();
                String answer = matcher7.group(3).trim();
                String explanation = matcher7.group(4).trim();

                //4.1 如果是多选则换多选页写入
                Matcher matcherMultipleChoicesType1 = compileMultipleChoicesType1.matcher(answer);
                Matcher matcher4 = compileMultipleChoicesType5.matcher(answer);
                Matcher matcher3 = compileMultipleChoicesType3.matcher(answer);
                if (matcherMultipleChoicesType1.find() || matcher3.find() || matcher4.find()) {
                    sheetPage = 1;
                    sheet = workbook.getSheetAt(sheetPage);
                    rowNum = findFirstEmptyRow(sheet);
                    row = sheet.createRow(rowNum++);
                }
                Matcher matcher8 = compileSingleChoicesType6.matcher(answer);
                Matcher matcherSingleChoicesType1 = compileSingleChoicesType1.matcher(answer);
                Matcher matcher2 = compileSingleChoicesType2.matcher(answer);
                Matcher matcher1 = compileSingleChoicesType3.matcher(answer);
                Matcher matcher5 = compileSingleChoicesType4.matcher(answer);
                Matcher matcher6 = compileSingleChoicesType5.matcher(answer);
                if (matcherSingleChoicesType1.find()
                        || matcher2.find()
                        || matcher1.find()
                        || matcher5.find()
                        || matcher6.find()
                        || matcher8.find()) {
                    sheetPage = 0;
                    sheet = workbook.getSheetAt(sheetPage);
                    rowNum = findFirstEmptyRow(sheet);
                    row = sheet.createRow(rowNum++);
                }

                //公共部分
                extracted(whetherParse, row, count, compileQuestion, fullQuestion, compileLetter, answer, explanation);

                FileOutputStream fos = new FileOutputStream(excelFilePath);
                workbook.write(fos);
                fos.close();

                // Close the doc
                document.close();
                fis.close();
                fisTemplate.close();
            }


        }

        //类型2
        for (String s : questionsType2) {
            if (s == null) {
                break;
            }
            sheet = workbook.getSheetAt(sheetPage);
            Matcher matcherMultipleChoices = compileMultipleChoices.matcher(s);
            Matcher matcherSingleChoices = compileSingleChoices.matcher(s);

            //4.1 单选
            if (matcherSingleChoices.find()) {
                sheetPage = 0;
                sheet = workbook.getSheetAt(sheetPage);
            }
            //4.2 如果是多选则换多选页写入
            if (matcherMultipleChoices.find()) {
                sheetPage = 1;
                sheet = workbook.getSheetAt(sheetPage);
            }
            rowNum = findFirstEmptyRow(sheet);
            Matcher matcherSpecificType2 = patternSpecificType2.matcher(s);
            if (matcherSpecificType2.find()) {
                XSSFRow row = sheet.createRow(rowNum++);
                count++;

                //4.3 提取
                String serialNumber = matcherSpecificType2.group(1).trim();
                String fullQuestion = matcherSpecificType2.group(2).trim();
                String answer = matcherSpecificType2.group(3).trim();
                String explanation = matcherSpecificType2.group(4).trim();

                //4.1 如果是多选则换多选页写入
                Matcher matcherMultipleChoicesType1 = compileMultipleChoicesType1.matcher(answer);
                if (matcherMultipleChoicesType1.find() && sheetPage == 0) {
                    sheetPage = 1;
                    sheet = workbook.getSheetAt(sheetPage);
                    row = sheet.createRow(rowNum++);
                }
                Matcher matcherSingleChoicesType1 = compileSingleChoicesType1.matcher(answer);
                if (matcherSingleChoicesType1.find() && sheetPage == 1) {
                    sheetPage = 0;
                    sheet = workbook.getSheetAt(sheetPage);
                    row = sheet.createRow(rowNum++);
                }

                //公共部分
                extracted(whetherParse, row, count, compileQuestion, fullQuestion, compileLetter, answer, explanation);


                FileOutputStream fos = new FileOutputStream(excelFilePath);
                workbook.write(fos);

                // Close the doc
                fos.close();
                document.close();
                fis.close();
                fisTemplate.close();
            }
        }

        //类型3
        for (String s3 : questionsType3) {
            if (s3 == null) {
                break;
            }
            sheet = workbook.getSheetAt(sheetPage);
            Matcher matcherMultipleChoices = compileMultipleChoices.matcher(s3);
            Matcher matcherSingleChoices = compileSingleChoices.matcher(s3);

            //4.1 单选
            if (matcherSingleChoices.find()) {
                sheetPage = 0;
                sheet = workbook.getSheetAt(sheetPage);
            }
            //4.2 如果是多选则换多选页写入
            if (matcherMultipleChoices.find()) {
                sheetPage = 1;
                sheet = workbook.getSheetAt(sheetPage);
            }
            rowNum = findFirstEmptyRow(sheet);
            Matcher matcherSpecificType3 = patternSpecificType3.matcher(s3);
            if (matcherSpecificType3.find()) {
                XSSFRow row = sheet.createRow(rowNum++);
                count++;

                //4.3 提取
                String serialNumber = matcherSpecificType3.group(1).trim();
                String fullQuestion = matcherSpecificType3.group(2).trim();
                String answer = matcherSpecificType3.group(3).trim();
                String explanation = matcherSpecificType3.group(4).trim();

                //4.1 如果是多选则换多选页写入
                Matcher matcherMultipleChoicesType1 = compileMultipleChoicesType1.matcher(answer);
                if (matcherMultipleChoicesType1.find() && sheetPage == 0) {
                    sheetPage = 1;
                    sheet = workbook.getSheetAt(sheetPage);
                    row = sheet.createRow(rowNum++);
                }
                Matcher matcherSingleChoicesType1 = compileSingleChoicesType1.matcher(answer);
                if (matcherSingleChoicesType1.find() && sheetPage == 1) {
                    sheetPage = 0;
                    sheet = workbook.getSheetAt(sheetPage);
                    row = sheet.createRow(rowNum++);
                }

                //公共部分
                extracted(whetherParse, row, count, compileQuestionType3, fullQuestion, compileLetter, answer, explanation);


                FileOutputStream fos = new FileOutputStream(excelFilePath);
                workbook.write(fos);

                // Close the doc
                fos.close();
                document.close();
                fis.close();
                fisTemplate.close();
            }
            //简答题
            Matcher matcher = compileShortAnswerType2.matcher(s3);
            if (matcher.find()) {
                sheetPage = 3;
                sheet = workbook.getSheetAt(sheetPage);
                rowNum = findFirstEmptyRow(sheet);
                XSSFRow row = sheet.createRow(rowNum++);
                count++;

                //4.3 提取
                String serialNumber = matcher.group(1).trim();
                String fullQuestion = matcher.group(2).trim();
                String answer = matcher.group(3).trim();

                //4.4 写入序号
                row.createCell(0).setCellValue(count);

                //4.5 写入题目
                row.createCell(1).setCellValue(fullQuestion);

                //4.6 写入答案
                row.createCell(2).setCellValue(answer); // 答案

                FileOutputStream fos = new FileOutputStream(excelFilePath);
                workbook.write(fos);
                // Close the doc
                fos.close();
                document.close();
                fis.close();
                fisTemplate.close();

            }

            //判断题
            Matcher matcher1 = compileJudgmentType.matcher(s3);
            if (matcher.find()) {
                sheetPage = 2;
                sheet = workbook.getSheetAt(sheetPage);
                rowNum = findFirstEmptyRow(sheet);
                XSSFRow row = sheet.createRow(rowNum++);
                count++;

                //4.3 提取
                String serialNumber = matcher.group(1).trim();
                String fullQuestion = matcher.group(2).trim();
                String answer = matcher.group(3).trim();
                String ex = matcher.group(4).trim();

                //4.4 写入序号
                row.createCell(0).setCellValue(count);

                //4.5 写入题目
                row.createCell(1).setCellValue(fullQuestion);

                //4.6 写入答案
                row.createCell(2).setCellValue(answer); // 答案

                row.createCell(3).setCellValue(ex); // 答案

                FileOutputStream fos = new FileOutputStream(excelFilePath);
                workbook.write(fos);
                // Close the doc
                fos.close();
                document.close();
                fis.close();
                fisTemplate.close();

            }
        }

        //类型4 案例题
        int rowCount = 0;
        for (String s4 : questions4) {
            if (s4 == null) {
                break;
            }
            Matcher matcherType4 = patternSpecificType4.matcher(s4);
            if (matcherType4.find()) {
                rowCount++;
                //将案例题干和题目分开
                String[] questionDetails4 = s4.split("(?=根据以上场景，回答下列问题)");
                Matcher matcherChoicesType4 = compileQuestionChoicesType4.matcher(questionDetails4[1]);
                Matcher matcherShortAnswerType4 = compileQuestionShortAnswerType4.matcher(questionDetails4[1]);
                //分开简答题与选择题
                //选择题
                String[] questionIsChoice = questionDetails4[1].split("(?=\\b\\d\\s*\\.)");

                sheetPage = 4;
                sheet = workbook.getSheetAt(sheetPage);
                rowNum = findFirstEmptyRow(sheet);
                XSSFRow row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(rowCount);
                row.createCell(1).setCellValue(questionDetails4[0]);

                int countNum=0;
                int countNum1=0;

                for (String s : questionIsChoice) {

                    Matcher matcher = pattern.matcher(s);
                    if (matcher.find()) {
                        String serialNumber = matcher.group(1).trim();
                        String fullQuestion = matcher.group(2).trim();
                        String answer = matcher.group(3).trim();
                        String explanation = matcher.group(4).trim();

                        //4.5 写入题目
                        row.createCell(2 + countNum).setCellValue(serialNumber + fullQuestion);
                        countNum++;
                        countNum++;
                        //4.6 写入答案
                        row.createCell(3 + countNum1).setCellValue(answer); // 答案
                        countNum1++;
                        countNum1++;
                        if (whetherParse == 1) {
                            //4.7 写入解析
                            row.createCell(9).setCellValue(explanation); // 试题解析
                        }
                        FileOutputStream fos = new FileOutputStream(excelFilePath);
                        workbook.write(fos);
                        // Close the doc
                        fos.close();
                        document.close();
                        fis.close();
                        fisTemplate.close();
                    }
                    //简答题
                    Matcher matcher1 = patternSpecificType4ShortAnswer.matcher(s);
                    if (matcher1.find()) {

                        //4.3 提取
                        String serialNumber = matcher1.group(1).trim();
                        String fullQuestion = matcher1.group(2).trim();
                        String answer = matcher1.group(3).trim();

                        //4.5 写入题目
                        row.createCell(2+countNum).setCellValue(serialNumber+fullQuestion);
                        countNum++;
                        countNum++;
                        //4.6 写入答案
                        row.createCell(3+countNum1).setCellValue(answer); // 答案
                        countNum1++;
                        countNum1++;
                        FileOutputStream fos = new FileOutputStream(excelFilePath);
                        workbook.write(fos);
                        // Close the doc
                        fos.close();
                        document.close();
                        fis.close();
                        fisTemplate.close();
                    }
                }



            }
        }


        log.info("Extraction and writing to Excel completed successfully!");

        fis.close();


        // 将workbook转换为字节数组
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        // 删除本地excel
        FileUtils.deleteFile(excelFilePath);
        return baos.toByteArray();

    }

    private static void extracted(int whetherParse, XSSFRow row, int count, Pattern compileQuestion, String fullQuestion, Pattern compileLetter, String answer, String explanation) {

        //4.4 写入序号
        row.createCell(0).setCellValue(count);

        //4.5 写入题目
        Matcher matcherQuestion = compileQuestion.matcher(fullQuestion);
        if (matcherQuestion.find()) {
            row.createCell(1).setCellValue(matcherQuestion.group(1).trim());
        } else {
            row.createCell(1).setCellValue(fullQuestion);
        }

        //4.7 写入选项
        String questionPart = fullQuestion;
        Pattern pattern = Pattern.compile("A.*[^a-zA-Z]B.*C.*D.*", Pattern.DOTALL);
        Matcher matcher = pattern.matcher(answer);
        if (matcher.find()) {
            String trim = matcher.group().trim();
            questionPart = answer;
        }

        //4.6 写入答案
        Pattern compileLetter1 = Pattern.compile("[a-fA-F]+,*\\s*[a-fA-F]*,*\\s*[a-fA-F]*,*\\s*[a-fA-F]*,*\\s*[a-fA-F]*,*\\s*[a-fA-F]*\\n", Pattern.DOTALL);
        Matcher matcherLetter = compileLetter.matcher(answer);
        Matcher matcherLetter1 = compileLetter1.matcher(answer);
        if (matcherLetter.find() && !questionPart.equals(answer)) {
            row.createCell(2).setCellValue(matcherLetter.group()); // 答案
        } else if (matcherLetter1.find() && questionPart.equals(answer)) {
            row.createCell(2).setCellValue(matcherLetter1.group());
        } else {
            row.createCell(1).setCellValue(answer);
        }


        String[] parts = questionPart.split("\\n");
        for (int i = 1; i < parts.length; i++) {
            if (parts[i].length() < 2) {
                continue;
            }
            if (parts[i].startsWith("A")) {
                row.createCell(3).setCellValue(parts[i].substring(2).trim()); // 选项A
            } else if (parts[i].startsWith("B")) {
                row.createCell(4).setCellValue(parts[i].substring(2).trim()); // 选项B
            } else if (parts[i].startsWith("C")) {
                row.createCell(5).setCellValue(parts[i].substring(2).trim()); // 选项C
            } else if (parts[i].startsWith("D")) {
                row.createCell(6).setCellValue(parts[i].substring(2).trim()); // 选项D
            } else if (parts[i].startsWith("E")) {
                row.createCell(7).setCellValue(parts[i].substring(2).trim()); // 选项E
            } else if (parts[i].startsWith("F")) {
                row.createCell(8).setCellValue(parts[i].substring(2).trim()); // 选项F
            }
        }
        if (whetherParse == 1) {
            //4.7 写入解析
            row.createCell(9).setCellValue(explanation); // 试题解析
        }
        if (isCellEmpty(row.getCell(1))
                || isCellEmpty(row.getCell(2))
                || isCellEmpty(row.getCell(3))
                || isCellEmpty(row.getCell(4))
                || isCellEmpty(row.getCell(5))
                || isCellEmpty(row.getCell(6))
        ) {
            clearRowCells(row);
        }


    }

    /**
     * 比较数组
     *
     * @param arr1
     * @param arr2
     * @param arr3
     */
    public static void compareAndClearSmallestTwo(String[] arr1, String[] arr2, String[] arr3) {
        int len1 = arr1.length;
        int len2 = arr2.length;
        int len3 = arr3.length;

        String[] smallest;
        String[] secondSmallest;

        if (len1 <= len2 && len1 <= len3) {
            smallest = arr1;
            secondSmallest = len2 <= len3 ? arr2 : arr3;
        } else if (len2 <= len1 && len2 <= len3) {
            smallest = arr2;
            secondSmallest = len1 <= len3 ? arr1 : arr3;
        } else {
            smallest = arr3;
            secondSmallest = len1 <= len2 ? arr1 : arr2;
        }

        clearArray(smallest);
        clearArray(secondSmallest);
    }

    /**
     * 清空数组
     *
     * @param array
     */
    private static void clearArray(String[] array) {
        for (int i = 0; i < array.length; i++) {
            array[i] = null;
        }
    }


    // 检测空行所在行数
    private static int findFirstEmptyRow(Sheet sheet) {
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if (row == null || isEmptyRow(row)) {
                return i;
            }
        }
        // 如果没有找到空行，则在最后一行之后写入
        return lastRowNum + 1;
    }

    private static boolean isEmptyRow(Row row) {
        for (Cell cell : row) {
            if (cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }
        return true;
    }

    /**
     * 清空指定行上的所有单元格，但保留行本身。
     *
     * @param row 要被清空的行
     */
    public static void clearRowCells(Row row) {
        if (row != null) {
            // 获取行中最后一个单元格的索引，通常表示单元格的数量
            int lastCellNum = row.getLastCellNum();
            for (int i = 0; i < lastCellNum; i++) {
                // 根据单元格的索引获取单元格
                Cell cell = row.getCell(i, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                if (cell != null) {
                    // 清空单元格的内容
//                    cell.setCellValue("");
//                    // 设置单元格类型为字符串，确保空值被正确保存
//                    cell.setCellType(CellType.STRING);
                    row.removeCell(cell);
                }
            }
        }
    }

    /**
     * 判断单元格是不是空的
     *
     * @param cell
     * @return
     */
    private static boolean isCellEmpty(Cell cell) {
        if (cell == null) {
            // 如果单元格对象本身是null，也认为是空的
            return true;
        }

        CellType cellType = cell.getCellType();

        switch (cellType) {
            case BLANK:
                // 如果单元格类型是BLANK，认为是空的
                return true;
            case STRING:
                // 如果单元格类型是STRING，检查其值是否为空字符串
                return cell.getStringCellValue().isEmpty();
            case NUMERIC:
                // 如果单元格类型是NUMERIC，检查其值是否为0
                // 注意：这里假设0被视为“空”。如果需要，可以调整逻辑
                return cell.getNumericCellValue() == 0;
            case BOOLEAN:
            case ERROR:
                // BOOLEAN和ERROR类型的单元格通常不视为“空”
                return false;
            case FORMULA:
                // 对于公式单元格，需要根据计算结果判断
                return false;
            default:
                // 如果单元格类型未知，这里可以抛出异常或返回默认值
                throw new IllegalArgumentException("Unknown cell type: " + cellType);
        }
    }


}
